from django.db.models import Q
import logging
from django.utils import timezone
from django.db.models import Sum
from datetime import timedelta, datetime

from django.db import connection


import tsl_backend.frontend_api.models.order_routing as order_routing
import tsl_backend.frontend_api.models.workorder as workorders
import tsl_backend.users.models as users

def check_due_month(order_route):
    due_month=''

    if order_route.order_complete_date:
        if order_route.order_complete_date > order_route.workorder.due_date:
            due_month="OVERD"
    
    return due_month

def check_now_role_of_flow(route_detail_obj):
    """
        正再进行的flow显示当前执行到的角色
        已经完成的flow显示最后一个角色
        如果没有完成的flow则显示空
    """
    record_obj=route_detail_obj.record_set.last()

    if route_detail_obj.status == 2:
        routing = route_detail_obj.order_route
        if routing.holder:
            return routing.holder.role.chinese_name 

    if record_obj:
        if record_obj.stone_check:
            return users.Role.objects.get(name="StoneQC").chinese_name
        elif record_obj.leader_check:
            return users.Role.objects.get(name="Teamleader").chinese_name
        elif record_obj.leader_check:
            return users.Role.objects.get(name="QC").chinese_name
        else:
            return users.Role.objects.get(name="WORKER").chinese_name
    return ''

def check_last_role_of_flow(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:

        if record_obj.stone_check:
            return users.Role.objects.get(name="StoneQC").chinese_name
        elif record_obj.leader_check:
            return users.Role.objects.get(name="Teamleader").chinese_name
        elif record_obj.leader_check:
            return users.Role.objects.get(name="QC").chinese_name
        else:
            return users.Role.objects.get(name="WORKER").chinese_name
    return ''

def check_last_worker_code(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.worker:
            if record_obj.worker.account:
                return record_obj.worker.account.user_code
    return ''


def check_last_worker_name(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.worker:
            if record_obj.worker.account:
                return record_obj.worker.account.django_user.cnName
    return ''
                  
def check_worker_count(route_detail_obj):
    count=route_detail_obj.record_set.count()
    return count

def check_worker_start_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.first()
    if record_obj:
        if record_obj.worker:
            worker_action_record_objs=record_obj.worker.workeractionrecord_set.all()
            
            if worker_action_record_objs.exists():
                worker_action_record_obj=worker_action_record_objs.first()
                # logging.info(f"Worker Start Time: {}")
                return worker_action_record_obj.time
    return ''

def check_worker_pause_duration(route_detail_obj):
    record_objs=route_detail_obj.record_set.all()
    pause_duration = timedelta()  
    for obj in record_objs:
        if obj.worker:
            if obj.worker.pause_duration:
                pause_duration += obj.worker.pause_duration
    return timedelta(hours=round(pause_duration.total_seconds()/3600,2))
                  

def check_worker_complete_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj:
            if record_obj.worker:
                worker_action_record_objs=record_obj.worker.workeractionrecord_set.all()
                
                if worker_action_record_objs.exists():
                    worker_action_record_objs=worker_action_record_objs.filter(action=3)
                    if worker_action_record_objs.exists():
                        return worker_action_record_objs.last().time
    return ''


def check_total_worker_duration(route_detail_obj):
    c_date = check_worker_complete_datetime(route_detail_obj)
    a_date = check_worker_start_datetime(route_detail_obj)

    total_worker_inprogress_duration=''

    if c_date and a_date:
        total_worker_inprogress_duration = round((c_date-a_date).total_seconds()/3600,2)
    return total_worker_inprogress_duration
     


def check_total_worker_inprogress_duration(route_detail_obj):
    c_date = check_worker_complete_datetime(route_detail_obj)
    a_date = check_worker_start_datetime(route_detail_obj)
    b_duration = check_worker_pause_duration(route_detail_obj)

    if c_date and a_date and b_duration:
        # total_worker_inprogress_duration = datetime.strptime(c_date, "%Y.%m.%d %H:%M:%S") - datetime.strptime(a_date, "%Y.%m.%d %H:%M:%S") - b_duration
        total_worker_inprogress_duration = c_date-a_date- b_duration
    else:
        total_worker_inprogress_duration=''
    return total_worker_inprogress_duration


def check_last_qc_code(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.qc_check:
            if record_obj.qc_check.account:
                return record_obj.qc_check.account.user_code
    return ''


def check_last_qc_name(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.qc_check:
            if record_obj.qc_check.account:
                return record_obj.qc_check.account.django_user.cnName
    return ''

def check_qc_start_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.first()
    if record_obj:
        if record_obj.qc_check:
            return record_obj.qc_check.formatted_received_at()
    return ''

def check_qc_complete_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.qc_check:
            return record_obj.qc_check.formatted_check_at()
    return ''
    
def check_total_qc_duration(route_detail_obj):
    start_date=check_qc_start_datetime(route_detail_obj)
    complete_date=check_qc_complete_datetime(route_detail_obj)
    
    if complete_date and start_date:
        logging.info(f"{complete_date} - {start_date}, {type(complete_date)}, {type(start_date)}")
        qc_duration = datetime.strptime(complete_date, "%Y.%m.%d %H:%M:%S") - datetime.strptime(start_date, "%Y.%m.%d %H:%M:%S")
        return round(qc_duration.total_seconds()/3600,2)
        # return qc_duration
    return ''
    
def check_last_leader_code(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.leader_check:
            if record_obj.leader_check.account:
                return record_obj.leader_check.account.user_code
    return ''
    


def check_last_leader_name(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.leader_check:
            if record_obj.leader_check.account:
                return record_obj.leader_check.account.django_user.cnName
    return ''


def check_leader_start_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.first()
    if record_obj:
        if record_obj.leader_check:
            return record_obj.leader_check.formatted_received_at()
    return ''

def check_leader_complete_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.leader_check:
            return record_obj.leader_check.formatted_check_at()
    return ''
    
def check_total_leader_duration(route_detail_obj):
    start_date=check_leader_start_datetime(route_detail_obj)
    complete_date=check_leader_complete_datetime(route_detail_obj)
    
    if complete_date and start_date:
        leader_duration = datetime.strptime(complete_date, "%Y.%m.%d %H:%M:%S") - datetime.strptime(start_date, "%Y.%m.%d %H:%M:%S")
        return round(leader_duration.total_seconds()/3600,2)
        # return leader_duration
    return ''
                

def check_last_stone_code(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.stone_check:
            if record_obj.stone_check.account:
                return record_obj.stone_check.account.user_code
    return ''


def check_last_stone_name(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.stone_check:
            if record_obj.stone_check.account:
                return record_obj.stone_check.account.django_user.cnName
    return ''
                  

def check_stone_start_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.first()
    if record_obj:
        if record_obj.stone_check:
            return record_obj.stone_check.formatted_received_at()
    else:
        return ''

def check_stone_complete_datetime(route_detail_obj):
    record_obj=route_detail_obj.record_set.last()
    if record_obj:
        if record_obj.stone_check:
            return record_obj.stone_check.formatted_check_at()
    else:
        return ''
    
def check_total_stone_duration(route_detail_obj):
    start_date=check_stone_start_datetime(route_detail_obj)
    complete_date=check_stone_complete_datetime(route_detail_obj)
    
    if complete_date and start_date:
        stone_duration = datetime.strptime(complete_date, "%Y.%m.%d %H:%M:%S") - datetime.strptime(start_date, "%Y.%m.%d %H:%M:%S")
        return round(stone_duration.total_seconds()/3600,2)
        # return stone_duration
    else:
        return ''
                
def check_dispatcher_receive_date(route_detail_obj):
    if route_detail_obj.dispatcher:
        return route_detail_obj.dispatcher.formatted_end_at()
    return ''

def filter_leadtime_objs(account_id, source, order, flow, dept):
    query = Q()
    
    route_query = Q()

    # if site:
    #     query &=  Q(workorder__site_id=site)
    if source:
        query &= Q(workorder__source=source)

    if order:
        query &=  Q(workorder__order=order)
        
    if flow:
        route_query &=  Q(flow_id=flow, status__in=[order_routing.RouteDetail.StatusChoices.In_Progress,order_routing.RouteDetail.StatusChoices.Complete])

    if dept:
        route_query &= Q(flow__department__code=dept)

    if account_id:
        logging.info(f"Query leadtime report for account {account_id}")
        account = users.Account.objects.get(pk=account_id)

        routing_objs = order_routing.OrderRouting.objects.filter(query)

        route_detail_objs = order_routing.RouteDetail.objects.filter(order_route__in=routing_objs).filter(route_query)

        logging.info(f"Query for leadtime report: {query}")
        logging.info(f"No. of record for leadtime report: {routing_objs.count()}")

        return route_detail_objs
    else:
        return None
        
def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]
def my_custom_sql(order_list):
    logging.info(f"Custom sql, select order_list {tuple(order_list)} from fgr_trackjw")
    with connection.cursor() as c:
        query = "SELECT * FROM public.fgr_trackjw WHERE fgrnum IN %s"
        #query = "SELECT * FROM public.fgr_trackjw"
        c.execute(query, ((tuple(order_list),)))
        #c.execute(query)
        rows = dictfetchall(c)
        return rows
        #   cursor.execute("""
        #     select max(id) from sacem_mtraw sm where train_name=%s;
        # """,(train_name,))


def parse_leadtime_data(page_detail_objs,sort_seq):
    order_list = []
    for obj in page_detail_objs:
        if str(obj.order_route.workorder.order) not in order_list:
            order_list.append(str(obj.order_route.workorder.order))
    

    rows = my_custom_sql(order_list)
    dict = {
        row['fgrnum']: row
        for row in rows
    }
    # print(dict)

    data = [
            {
            "site": obj.order_route.workorder.site.name if obj.order_route.workorder.site else None,
            "source": obj.order_route.workorder.source or None,
            # "fgNum": obj.order_route.workorder.ori_fg_article, old
            "fgNum": obj.order_route.workorder.order, # new
            "seq": obj.seq,
            "orderType":  dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',
            "orddate":  dict[obj.order_route.workorder.order]['orddate'] if obj.order_route.workorder.order in dict else '',
            "duedat": obj.order_route.workorder.formatted_due_date(),
            "dueMonth": check_due_month(obj.order_route),
            "designNum": obj.order_route.workorder.design,
            # "pono": obj.order_route.workorder.order, old
            "pono": dict[obj.order_route.workorder.order]['pono'] if obj.order_route.workorder.order in dict else '', # new
            "stoneItem":  dict[obj.order_route.workorder.order]['stoneitm'] if obj.order_route.workorder.order in dict else '',
            "podesc": obj.order_route.workorder.description,
            "goods_type": obj.order_route.workorder.goods_type,
            "catcde":  dict[obj.order_route.workorder.order]['catcde'] if obj.order_route.workorder.order in dict else '',
            "ordqty": obj.order_route.workorder.quantity,
            "dept1":  dict[obj.order_route.workorder.order]['dept1'] if obj.order_route.workorder.order in dict else '',
            "dept11":  dict[obj.order_route.workorder.order]['dept11'] if obj.order_route.workorder.order in dict else '',
            "datfrm1":  dict[obj.order_route.workorder.order]['datfrm1'] if obj.order_route.workorder.order in dict else '',
            "dept3":  dict[obj.order_route.workorder.order]['dept3'] if obj.order_route.workorder.order in dict else '',
            "dept31":  dict[obj.order_route.workorder.order]['dept31'] if obj.order_route.workorder.order in dict else '',
            "stpdays":  dict[obj.order_route.workorder.order]['stpdays'] if obj.order_route.workorder.order in dict else '',
            "dept4":  dict[obj.order_route.workorder.order]['dept4'] if obj.order_route.workorder.order in dict else '',
            "datfrm4":  dict[obj.order_route.workorder.order]['datfrm4'] if obj.order_route.workorder.order in dict else '',
            "gldcde":  dict[obj.order_route.workorder.order]['gldcde'] if obj.order_route.workorder.order in dict else '',
            "complet": obj.order_route.order_complete_date if obj.order_route.order_complete_date else '',
            "overdue":  dict[obj.order_route.workorder.order]['overdue'] if obj.order_route.workorder.order in dict else '',
            "pfnum":  dict[obj.order_route.workorder.order]['pfnum'] if obj.order_route.workorder.order in dict else '',
            "deptr":  dict[obj.order_route.workorder.order]['deptr'] if obj.order_route.workorder.order in dict else '',
            "ringType": obj.order_route.workorder.ring_type,
            "readyDate": obj.order_route.workorder.ready_date,
            "productcde":  dict[obj.order_route.workorder.order]['productcde'] if obj.order_route.workorder.order in dict else '',
            "salesMix":  dict[obj.order_route.workorder.order]['sales_mix'] if obj.order_route.workorder.order in dict else '',
            "productMix": dict[obj.order_route.workorder.order]['prduct_mix'] if obj.order_route.workorder.order in dict else '',
            "mouldNo": obj.order_route.workorder.mould,
            "sbudate":  dict[obj.order_route.workorder.order]['sbudate'] if obj.order_route.workorder.order in dict else '',
            "podesc2":  dict[obj.order_route.workorder.order]['podesc2'] if obj.order_route.workorder.order in dict else '',
            "orderType2": dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',  
            "flow": obj.flow.chinese_name,
            "role": check_now_role_of_flow(obj),  
            "worker_code": check_last_worker_code(obj),
            "worker_name": check_last_worker_name(obj),
            "worker_counter": check_worker_count(obj),
            "worker_start": check_worker_start_datetime(obj),
            "worker_pause": check_worker_pause_duration(obj),
            "worker_finish": check_worker_complete_datetime(obj),
            "worker_duration_a": check_total_worker_duration(obj),
            "worker_duration_b":  check_total_worker_inprogress_duration(obj),
            "qc_code": check_last_qc_code(obj),
            "qc_name": check_last_qc_name(obj),
            # "qc_counter": "",
            "qc_start": check_qc_start_datetime(obj),
            "qc_finish": check_qc_complete_datetime(obj),
            "qc_duration_a": check_total_qc_duration(obj),

            "leader_code": check_last_leader_code(obj),
            "leader_name": check_last_leader_name(obj),
            # "leader_counter":"",
            "leader_start": check_leader_start_datetime(obj),
            "leader_finish": check_leader_complete_datetime(obj),
            "leader_duration_a": check_total_leader_duration(obj),

            
            "stone_code": check_last_stone_code(obj),
            "stone_name": check_last_stone_name(obj),
            # "stone_counter":"",
            "stone_start": check_stone_start_datetime(obj),
            "stone_finish": check_stone_complete_datetime(obj),
            "stone_duration_a": check_total_stone_duration(obj),

            "dispatcher_start": check_dispatcher_receive_date(obj),
        } for obj in page_detail_objs
    ]

    logging.info(f"sort_seq in sorted data: {sort_seq}")
    
    try:
        if sort_seq == 'role':
            sorted_data = sorted(data, key=lambda x: (x['role']))
        elif sort_seq == '-role':
            sorted_data = sorted(data, key=lambda x: (x['role']), reverse=True)
        elif sort_seq == 'worker_code':
            sorted_data = sorted(data, key=lambda x: (x['worker_code']))
        elif sort_seq == '-worker_code':
            sorted_data = sorted(data, key=lambda x: (x['worker_code']), reverse=True)
        elif sort_seq == 'worker_name':
            sorted_data = sorted(data, key=lambda x: (x['worker_name']))
        elif sort_seq == '-worker_name':
            sorted_data = sorted(data, key=lambda x: (x['worker_name']), reverse=True)
        elif sort_seq == 'worker_counter':
            sorted_data = sorted(data, key=lambda x: (x['worker_counter']))
        elif sort_seq == '-worker_counter':
            sorted_data = sorted(data, key=lambda x: (x['worker_counter']), reverse=True)
        elif sort_seq == 'worker_start':
            sorted_data = sorted(data, key=lambda x: (x['worker_start'] if x['worker_start'] else datetime.min))
        elif sort_seq == '-worker_start':
            sorted_data = sorted(data, key=lambda x: (x['worker_start'] if x['worker_start'] else datetime.min), reverse=True)
        elif sort_seq == 'worker_pause':
            sorted_data = sorted(data, key=lambda x: (x['worker_pause']))
        elif sort_seq == '-worker_pause':
            sorted_data = sorted(data, key=lambda x: (x['worker_pause']), reverse=True)
        elif sort_seq == 'worker_finish':
            sorted_data = sorted(data, key=lambda x: (x['worker_finish'] if x['worker_finish'] else datetime.min))
        elif sort_seq == '-worker_finish':
            sorted_data = sorted(data, key=lambda x: (x['worker_finish'] if x['worker_finish'] else datetime.min), reverse=True)
        elif sort_seq == 'worker_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['worker_duration_a']))
        elif sort_seq == '-worker_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['worker_duration_a']), reverse=True)
        elif sort_seq == 'worker_duration_b':
            sorted_data = sorted(data, key=lambda x: (x['worker_duration_b']))
        elif sort_seq == '-worker_duration_b':
            sorted_data = sorted(data, key=lambda x: (x['worker_duration_b']), reverse=True)
        elif sort_seq == 'qc_code':
            sorted_data = sorted(data, key=lambda x: (x['qc_code']))
        elif sort_seq == '-qc_code':
            sorted_data = sorted(data, key=lambda x: (x['qc_code']), reverse=True)
        elif sort_seq == 'qc_name':
            sorted_data = sorted(data, key=lambda x: (x['qc_name']))
        elif sort_seq == '-qc_name':
            sorted_data = sorted(data, key=lambda x: (x['qc_name']), reverse=True)
        elif sort_seq == 'qc_start':
            sorted_data = sorted(data, key=lambda x: (x['qc_start'] if x['qc_start'] else datetime.min))
        elif sort_seq == '-qc_start':
            sorted_data = sorted(data, key=lambda x: (x['qc_start'] if x['qc_start'] else datetime.min), reverse=True)
        elif sort_seq == 'qc_finish':
            sorted_data = sorted(data, key=lambda x: (x['qc_finish'] if x['qc_finish'] else datetime.min))
        elif sort_seq == '-qc_finish':
            sorted_data = sorted(data, key=lambda x: (x['qc_finish'] if x['qc_finish'] else datetime.min), reverse=True)
        elif sort_seq == 'qc_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['qc_duration_a']))
        elif sort_seq == '-qc_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['qc_duration_a']), reverse=True)
        elif sort_seq == 'leader_code':
            sorted_data = sorted(data, key=lambda x: (x['leader_code']))
        elif sort_seq == '-leader_code':
            sorted_data = sorted(data, key=lambda x: (x['leader_code']), reverse=True)
        elif sort_seq == 'leader_name':
            sorted_data = sorted(data, key=lambda x: (x['leader_name']))
        elif sort_seq == '-leader_name':
            sorted_data = sorted(data, key=lambda x: (x['leader_name']), reverse=True)
        elif sort_seq == 'leader_start':
            sorted_data = sorted(data, key=lambda x: (x['leader_start'] if x['leader_start'] else datetime.min))
        elif sort_seq == '-leader_start':
            sorted_data = sorted(data, key=lambda x: (x['leader_start'] if x['leader_start'] else datetime.min), reverse=True)
        elif sort_seq == 'leader_finish':
            sorted_data = sorted(data, key=lambda x: (x['leader_finish'] if x['leader_finish'] else datetime.min))
        elif sort_seq == '-leader_finish':
            sorted_data = sorted(data, key=lambda x: (x['leader_finish'] if x['leader_finish'] else datetime.min), reverse=True)
        elif sort_seq == 'leader_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['leader_duration_a']))
        elif sort_seq == '-leader_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['leader_duration_a']), reverse=True)
        elif sort_seq == 'stone_code':
            sorted_data = sorted(data, key=lambda x: (x['stone_code']))
        elif sort_seq == '-stone_code':
            sorted_data = sorted(data, key=lambda x: (x['stone_code']), reverse=True)
        elif sort_seq == 'stone_name':
            sorted_data = sorted(data, key=lambda x: (x['stone_name']))
        elif sort_seq == '-stone_name':
            sorted_data = sorted(data, key=lambda x: (x['stone_name']), reverse=True)
        elif sort_seq == 'stone_start':
            sorted_data = sorted(data, key=lambda x: (x['stone_start'] if x['stone_start'] else datetime.min))
        elif sort_seq == '-stone_start':
            sorted_data = sorted(data, key=lambda x: (x['stone_start'] if x['stone_start'] else datetime.min), reverse=True)
        elif sort_seq == 'stone_finish':
            sorted_data = sorted(data, key=lambda x: (x['stone_finish'] if x['stone_finish'] else datetime.min))
        elif sort_seq == '-stone_finish':
            sorted_data = sorted(data, key=lambda x: (x['stone_finish'] if x['stone_finish'] else datetime.min), reverse=True)
        elif sort_seq == 'stone_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['stone_duration_a']))
        elif sort_seq == '-stone_duration_a':
            sorted_data = sorted(data, key=lambda x: (x['stone_duration_a']), reverse=True)
        elif sort_seq == 'dispatcher_start':
            sorted_data = sorted(data, key=lambda x: (x['dispatcher_start']))
        elif sort_seq == '-dispatcher_start':
            sorted_data = sorted(data, key=lambda x: (x['dispatcher_start']), reverse=True)
        else:
            sorted_data = sorted(data, key=lambda x: (x['pono'], x['seq']))

    except Exception as e:
        logging.error(f"Exception for sorting leadtime report: {e}")
        sorted_data = sorted(data, key=lambda x: (x['pono'], x['seq']))

    return sorted_data

def parse_leadtime_csv_data(page_detail_objs):
    order_list = []
    for obj in page_detail_objs:
        if str(obj.order_route.workorder.order) not in order_list:
            order_list.append(str(obj.order_route.workorder.order))
    

    rows = my_custom_sql(order_list)
    dict = {
        row['fgrnum']: row
        for row in rows
    }
    # print(dict)

    data = [
            {
            "site": obj.order_route.workorder.site.name if obj.order_route.workorder.site else None,
            "source": obj.order_route.workorder.source or None,
            # "fgNum": obj.order_route.workorder.ori_fg_article, old
            "fgNum": obj.order_route.workorder.order, # new
            "seq": obj.seq,
            "orderType":  dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',
            "orddate":  dict[obj.order_route.workorder.order]['orddate'] if obj.order_route.workorder.order in dict else '',
            "duedat": obj.order_route.workorder.formatted_due_date(),
            "dueMonth": check_due_month(obj.order_route),
            "designNum": obj.order_route.workorder.design,
            # "pono": obj.order_route.workorder.order, old
            "pono": dict[obj.order_route.workorder.order]['pono'] if obj.order_route.workorder.order in dict else '', # new
            "stoneItem":  dict[obj.order_route.workorder.order]['stoneitm'] if obj.order_route.workorder.order in dict else '',
            "podesc": obj.order_route.workorder.description,
            "goods_type": obj.order_route.workorder.goods_type,
            "catcde":  dict[obj.order_route.workorder.order]['catcde'] if obj.order_route.workorder.order in dict else '',
            "ordqty": obj.order_route.workorder.quantity,
            "dept1":  dict[obj.order_route.workorder.order]['dept1'] if obj.order_route.workorder.order in dict else '',
            "dept11":  dict[obj.order_route.workorder.order]['dept11'] if obj.order_route.workorder.order in dict else '',
            "datfrm1":  dict[obj.order_route.workorder.order]['datfrm1'] if obj.order_route.workorder.order in dict else '',
            "dept3":  dict[obj.order_route.workorder.order]['dept3'] if obj.order_route.workorder.order in dict else '',
            "dept31":  dict[obj.order_route.workorder.order]['dept31'] if obj.order_route.workorder.order in dict else '',
            "stpdays":  dict[obj.order_route.workorder.order]['stpdays'] if obj.order_route.workorder.order in dict else '',
            "dept4":  dict[obj.order_route.workorder.order]['dept4'] if obj.order_route.workorder.order in dict else '',
            "datfrm4":  dict[obj.order_route.workorder.order]['datfrm4'] if obj.order_route.workorder.order in dict else '',
            "gldcde":  dict[obj.order_route.workorder.order]['gldcde'] if obj.order_route.workorder.order in dict else '',
            "complet": obj.order_route.order_complete_date if obj.order_route.order_complete_date else '',
            "overdue":  dict[obj.order_route.workorder.order]['overdue'] if obj.order_route.workorder.order in dict else '',
            "pfnum":  dict[obj.order_route.workorder.order]['pfnum'] if obj.order_route.workorder.order in dict else '',
            "deptr":  dict[obj.order_route.workorder.order]['deptr'] if obj.order_route.workorder.order in dict else '',
            "ringType": obj.order_route.workorder.ring_type,
            "readyDate": obj.order_route.workorder.ready_date,
            "productcde":  dict[obj.order_route.workorder.order]['productcde'] if obj.order_route.workorder.order in dict else '',
            "salesMix":  dict[obj.order_route.workorder.order]['sales_mix'] if obj.order_route.workorder.order in dict else '',
            "productMix": dict[obj.order_route.workorder.order]['prduct_mix'] if obj.order_route.workorder.order in dict else '',
            "mouldNo": obj.order_route.workorder.mould,
            "sbudate":  dict[obj.order_route.workorder.order]['sbudate'] if obj.order_route.workorder.order in dict else '',
            "podesc2":  dict[obj.order_route.workorder.order]['podesc2'] if obj.order_route.workorder.order in dict else '',
            "orderType2": dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',  
            "flow": obj.flow.chinese_name,
            "role": check_now_role_of_flow(obj),  
            "worker_code": check_last_worker_code(obj),
            "worker_name": check_last_worker_name(obj),
            "worker_counter": check_worker_count(obj),
            "worker_start": check_worker_start_datetime(obj),
            "worker_pause": check_worker_pause_duration(obj),
            "worker_finish": check_worker_complete_datetime(obj),
            "worker_duration_a": check_total_worker_duration(obj),
            "worker_duration_b":  check_total_worker_inprogress_duration(obj),
            "qc_code": check_last_qc_code(obj),
            "qc_name": check_last_qc_name(obj),
            # "qc_counter": "",
            "qc_start": check_qc_start_datetime(obj),
            "qc_finish": check_qc_complete_datetime(obj),
            "qc_duration_a": check_total_qc_duration(obj),

            "leader_code": check_last_leader_code(obj),
            "leader_name": check_last_leader_name(obj),
            # "leader_counter":"",
            "leader_start": check_leader_start_datetime(obj),
            "leader_finish": check_leader_complete_datetime(obj),
            "leader_duration_a": check_total_leader_duration(obj),

            
            "stone_code": check_last_stone_code(obj),
            "stone_name": check_last_stone_name(obj),
            # "stone_counter":"",
            "stone_start": check_stone_start_datetime(obj),
            "stone_finish": check_stone_complete_datetime(obj),
            "stone_duration_a": check_total_stone_duration(obj),

            "dispatcher_start": check_dispatcher_receive_date(obj),
        } for obj in page_detail_objs
    ]

    return data

#  data = [
#             {
#                 "site": routing.workorder.site.name if routing.workorder.site else None,
#                 "fgr_num": routing.workorder.order,
#                 "order_type": "",
#                 "order_date": "",
#                 "due_date": routing.workorder.formatted_due_date(),
#                 "due_month": "overdue"
#                 if routing.workorder.due_date < timezone.now()
#                 else None,
#                 "design": routing.workorder.design,
#                 "po_no": "",
#                 "stone_item": "",  # TODO: none
#                 "po_desc": "",
#                 "goods_type": routing.workorder.goods_type,
#                 "catcde": "",  # TODO: none
#                 "order_quantity": routing.workorder.quantity,
#                 "dept1": "",  # TODO: none
#                 "dept11": "",  # TODO: none
#                 "datfrm1": "",  # TODO: none
#                 "dept3": "",  # TODO: none
#                 "dept31": "",  # TODO: none
#                 "stpdays": "",  # TODO: none
#                 "dept4": "",  # TODO: none
#                 "datfrm4": "",  # TODO: none
#                 "gldcde": "",  # TODO: none
#                 "complete_date": order_routing.RouteDetail.objects.get(
#                     order_route_id=routing, flow=flow
#                 ).dispatcher.end_at,
#                 "overdue": "",  # TODO: none
#                 "pf_num": "",  # TODO: none
#                 "deptr": "",  # TODO: none
#                 "ring_type": routing.workorder.ring_type,
#                 "ready_date": routing.workorder.ready_date,
#                 "product_cde": routing.workorder.master.fg_article,
#                 "sales_mix": "",  # TODO: none
#                 "product_mix": "",  # TODO: none
#                 "mould": routing.workorder.mould,
#                 "sbu_date": "",  # TODO: none
#                 "podesc2": "",  # TODO: none
#                 "order_type2": "",  # TODO: none
#                 "flow": order_routing.RouteDetail.objects.get(
#                     order_route=routing, flow_id=flow
#                 ).flow.name,
#                 "role": "",  # TODO: none
#                 "worker_code": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .worker.account.user_code,
#                 "worker_name": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .worker.account.django_user.cnName,
#                 "worker_counter": order_routing.WorkerActionRecord.objects.filter(
#                     worker_record=order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .worker
#                 ).count(),
#                 "worker_start": order_routing.WorkerActionRecord.objects.filter(
#                     worker_record=order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .worker,
#                     action=1,
#                 )
#                 .first()
#                 .time,
#                 "worker_pause": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .worker.pause_duration,
#                 "worker_finish": order_routing.WorkerActionRecord.objects.filter(
#                     worker_record=order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .worker,
#                     action=3,
#                 )
#                 .last()
#                 .time,
#                 "worker_duration_a": str(
#                     order_routing.WorkerActionRecord.objects.filter(
#                         worker_record=order_routing.Record.objects.filter(
#                             route_detail=order_routing.RouteDetail.objects.get(
#                                 order_route=routing, flow_id=flow
#                             )
#                         )
#                         .last()
#                         .worker,
#                         action=3,
#                     )
#                     .last()
#                     .time
#                     - order_routing.WorkerActionRecord.objects.filter(
#                         worker_record=order_routing.Record.objects.filter(
#                             route_detail=order_routing.RouteDetail.objects.get(
#                                 order_route=routing, flow_id=flow
#                             )
#                         )
#                         .last()
#                         .worker,
#                         action=1,
#                     )
#                     .first()
#                     .time
#                 ),
#                 "worker_duration_b": str(
#                     order_routing.WorkerActionRecord.objects.filter(
#                         worker_record=order_routing.Record.objects.filter(
#                             route_detail=order_routing.RouteDetail.objects.get(
#                                 order_route=routing, flow_id=flow
#                             )
#                         )
#                         .last()
#                         .worker,
#                         action=3,
#                     )
#                     .last()
#                     .time
#                     - order_routing.WorkerActionRecord.objects.filter(
#                         worker_record=order_routing.Record.objects.filter(
#                             route_detail=order_routing.RouteDetail.objects.get(
#                                 order_route=routing, flow_id=flow
#                             )
#                         )
#                         .last()
#                         .worker,
#                         action=1,
#                     )
#                     .first()
#                     .time
#                     - order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .worker.pause_duration
#                 ),
#                 "qc_code": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .qc_check.account.user_code,
#                 "qc_name": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .qc_check.account.django_user.cnName,
#                 "qc_counter": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     ),
#                     qc_check__isnull=False,
#                 ),
#                 "qc_start": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .first()
#                 .qc_check.check_at,
#                 "qc_finish": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .qc_check.check_at,
#                 "qc_duration_a": str(
#                     order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .qc_check.check_at
#                     - order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .first()
#                     .qc_check.check_at
#                 ),
#                 "leader_code": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .leader_check.account.user_code,
#                 "leader_name": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .leader_check.account.django_user.cnName,
#                 "leader_counter": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     ),
#                     leader_check__isnull=False,
#                 ),
#                 "leader_start": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .first()
#                 .leader_check.check_at,
#                 "leader_finish": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .leader_check.check_at,
#                 "leader_duration_a": str(
#                     order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .leader_check.check_at
#                     - order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .first()
#                     .leader_check.check_at
#                 ),
#                 "stone_code": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .stone_check.account.user_code,
#                 "stone_name": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .stone_check.account.django_user.cnName,
#                 "stone_counter": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     ),
#                     stone_check__isnull=False,
#                 ),
#                 "stone_start": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .first()
#                 .stone_check.check_at,
#                 "stone_finish": order_routing.Record.objects.filter(
#                     route_detail=order_routing.RouteDetail.objects.get(
#                         order_route=routing, flow_id=flow
#                     )
#                 )
#                 .last()
#                 .stone_check.check_at,
#                 "stone_duration_a": str(
#                     order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .last()
#                     .stone_check.check_at
#                     - order_routing.Record.objects.filter(
#                         route_detail=order_routing.RouteDetail.objects.get(
#                             order_route=routing, flow_id=flow
#                         )
#                     )
#                     .first()
#                     .stone_check.check_at
#                 ),
#                 "dispatcher_start": order_routing.RouteDetail.objects.get(
#                     order_route_id=routing, flow_id=flow
#                 ).dispatcher.start_at,
#             }
#             for routing in routing_objs
#         ]

def parse_leadtime_data_4_export(account_id, site, order, flow, dept):
    data={}
    query = Q()
    route_query = Q()

    if site:
        query &=  Q(workorder__site_id=site)
    if order:
        query &=  Q(workorder__order__icontains=order)
    
    if flow:
        route_query &= Q(flow_id=flow)

    if dept:
        route_query &= Q(flow__department__code=dept)

    if account_id:
        logging.info(f"Query leadtime report for account {account_id}")
        account = users.Account.objects.get(pk=account_id)

        routing_objs = order_routing.OrderRouting.objects.filter(query)

        route_detail_objs = order_routing.RouteDetail.objects.filter(order_route__in=routing_objs).filter(route_query)

        logging.info(f"Query for leadtime report: {query}")
        logging.info(f"No. of record for leadtime report: {routing_objs.count()}")

        pono_list = []
        for obj in route_detail_objs:
            if str(obj.order_route.workorder.order) not in pono_list:
                pono_list.append(str(obj.order_route.workorder.order))
        

        rows = my_custom_sql(pono_list)
        dict = {
            row['pono']: row
            for row in rows
        }

        print(dict)

        
        data = [
               {
                "site": obj.order_route.workorder.site.name if obj.order_route.workorder.site else None,
                # "fgNum": obj.order_route.workorder.ori_fg_article, old
                "fgNum": obj.order_route.workorder.order, # new
                "orderType": dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',
                "orddate": dict[obj.order_route.workorder.order]['orddate'] if obj.order_route.workorder.order in dict else '',
                "duedat": obj.order_route.workorder.formatted_due_date(),
                "dueMonth": check_due_month(obj.order_route),
                "designNum": obj.order_route.workorder.design,
                # "pono": obj.order_route.workorder.order, old
                "pono": dict[obj.order_route.workorder.order]['pono'] if obj.order_route.workorder.order in dict else '', # new
                "stoneItem":  dict[obj.order_route.workorder.order]['stoneitm'] if obj.order_route.workorder.order in dict else '',
                "podesc": obj.order_route.workorder.description,
                "goods_type": obj.order_route.workorder.goods_type,
                "catcde":  dict[obj.order_route.workorder.order]['catcde'] if obj.order_route.workorder.order in dict else '',
                "ordqty": obj.order_route.workorder.quantity,
                "dept1":  dict[obj.order_route.workorder.order]['dept1'] if obj.order_route.workorder.order in dict else '',
                "dept11":  dict[obj.order_route.workorder.order]['dept11'] if obj.order_route.workorder.order in dict else '',
                "datfrm1":  dict[obj.order_route.workorder.order]['datfrm1'] if obj.order_route.workorder.order in dict else '',
                "dept3":  dict[obj.order_route.workorder.order]['dept3'] if obj.order_route.workorder.order in dict else '',
                "dept31":  dict[obj.order_route.workorder.order]['dept31'] if obj.order_route.workorder.order in dict else '',
                "stpdays":  dict[obj.order_route.workorder.order]['stpdays'] if obj.order_route.workorder.order in dict else '',
                "dept4":  dict[obj.order_route.workorder.order]['dept4'] if obj.order_route.workorder.order in dict else '',
                "datfrm4":  dict[obj.order_route.workorder.order]['datfrm4'] if obj.order_route.workorder.order in dict else '',
                "gldcde":  dict[obj.order_route.workorder.order]['gldcde'] if obj.order_route.workorder.order in dict else '',
                "complet": obj.order_route.order_complete_date if obj.order_route.order_complete_date else '',
                "overdue":  dict[obj.order_route.workorder.order]['overdue'] if obj.order_route.workorder.order in dict else '',
                "pfnum":  dict[obj.order_route.workorder.order]['pfnum'] if obj.order_route.workorder.order in dict else '',
                "deptr":  dict[obj.order_route.workorder.order]['deptr'] if obj.order_route.workorder.order in dict else '',
                "ringType": obj.order_route.workorder.ring_type,
                "readyDate": obj.order_route.workorder.ready_date,
                "productcde":  dict[obj.order_route.workorder.order]['productcde'] if obj.order_route.workorder.order in dict else '',
                "salesMix":  dict[obj.order_route.workorder.order]['sales_mix'] if obj.order_route.workorder.order in dict else '',
                "productMix": dict[obj.order_route.workorder.order]['prduct_mix'] if obj.order_route.workorder.order in dict else '',
                "mouldNo": obj.order_route.workorder.mould,
                "sbudate":  dict[obj.order_route.workorder.order]['sbudate'] if obj.order_route.workorder.order in dict else '',
                "podesc2":  dict[obj.order_route.workorder.order]['podesc2'] if obj.order_route.workorder.order in dict else '',
                "orderType2": dict[obj.order_route.workorder.order]['ordertype'] if obj.order_route.workorder.order in dict else '',  
                "flow": obj.flow.chinese_name,
                "role": check_last_role_of_flow(obj),  # TODO: none
                "worker_code": check_last_worker_code(obj),
                "worker_name": check_last_worker_name(obj),
                "worker_counter": check_worker_count(obj),
                "worker_start": check_worker_start_datetime(obj),
                "worker_pause": check_worker_pause_duration(obj),
                "worker_finish": check_worker_complete_datetime(obj),
                "worker_duration_a": check_total_worker_duration(obj),
                "worker_duration_b":  check_total_worker_inprogress_duration(obj),
                "qc_code": check_last_qc_code(obj),
                "qc_name": check_last_qc_name(obj),
                # "qc_counter": "",
                "qc_start": check_qc_start_datetime(obj),
                "qc_finish": check_qc_complete_datetime(obj),
                "qc_duration_a": check_total_qc_duration(obj),

                "leader_code": check_last_leader_code(obj),
                "leader_name": check_last_leader_name(obj),
                # "leader_counter":"",
                "leader_start": check_leader_start_datetime(obj),
                "leader_finish": check_leader_complete_datetime(obj),
                "leader_duration_a": check_total_leader_duration(obj),

                
                "stone_code": check_last_stone_code(obj),
                "stone_name": check_last_stone_name(obj),
                # "stone_counter":"",
                "stone_start": check_stone_start_datetime(obj),
                "stone_finish": check_stone_complete_datetime(obj),
                "stone_duration_a": check_total_stone_duration(obj),

                "dispatcher_start": check_dispatcher_receive_date(obj),
            } for obj in route_detail_objs
        ]


    return data        